package com.czyxy;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by mike on 6/8/17.
 */
@Service
public class StudentFeeService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<StudentFee> getAllStudentFee(){
        String sql ="select c.xh studentCode ,c.xm studentName,c.jxnf inSchoolYear,c.bynf graduateYear,d.bjm classCode,d.bjmc className, e.xmm feeCode,e.xmmc feeName,b.sfnf feeYear,a.rq feeDate,a.sjs feeAmount,a.sjh feeNo " +
                ",b.yss shouldChargeAmount, b.dzs adjustAmount " +
                "   from XsssMx a,   XssfMx b,  Xszd c,Xsbj  d,sfxm e " +
                "  where a.xh=c.xh  and a.xh=b.xh and a.sfnf=b.sfnf and a.xmm=b.xmm and a.xmm=e.xmm and b.sfnf<='2015'  and c.bjm=d.bjm and b.xh=c.xh " +
                "\n";
        List<StudentFee> feeList= jdbcTemplate.query(sql, new RowMapper<StudentFee>() {
            @Override
            public StudentFee mapRow(ResultSet rs, int i) throws SQLException {
                StudentFee fee = new StudentFee();
                fee.setStudentCode(rs.getString("studentCode").trim());
                fee.setStudentName(rs.getString("studentName").trim());
                fee.setClassCode(rs.getString("classCode").trim());
                fee.setClassName(rs.getString("className").trim());
                fee.setInSchoolYear(rs.getString("inSchoolYear").trim());
                fee.setGraduateYear(rs.getString("GraduateYear").trim());
                fee.setFeeCode(rs.getString("feeCode").trim());
                fee.setFeeName(rs.getString("feeName").trim());
                fee.setFeeAmount(rs.getString("feeAmount").trim());
                fee.setFeeYear(rs.getString("feeYear").trim());
                fee.setFeeDate(rs.getString("feeDate").trim());
                fee.setFeeNo(rs.getString("feeNo").trim());
                fee.setShouldChargeAmount(rs.getString("shouldChargeAmount").trim());
                fee.setAdjustAmount(rs.getString("adjustAmount").trim());
                return fee;
            }
        });
        return feeList;
    }
}
